/*	--- 使用JDBC --- 
(使用 HSQLDB Java关系数据库 用于 测试/演示)

我们在前面介绍【JDBC编程】时已经讲过，【Java程序】使用【JDBC接口】访问【关系数据库】的时候，需要以下几步：

	1. 创建【全局DataSource实例】，表示【数据库连接池】；
	2. 在需要【读写 数据库的方法内部】，按如下步骤【访问数据库】：
		2-1. 从【全局DataSource实例】获取【Connection实例】；
		2-2. 通过【Connection实例】创建【PreparedStatement实例】；
		2-3. 执行SQL语句。
			如果是[查询]，则通过【ResultSet】读取【结果集】;
			如果是[修改]，则获得【int结果】;


【正确编写JDBC代码的关键】是【使用try ... finally释放资源】

涉及到【事务的代码】需要【 正确提交/回滚事务 】。

--------------------------------------

【在Spring使用JDBC】，首先我们通过【IoC容器】创建并管理【一个DataSource实例】，然后，【Spring】提供了【一个JdbcTemplate】，可以方便地让我们操作JDBC

因此，通常情况下，我们会【实例化一个JdbcTemplate】。
顾名思义，【JdbcTemplate这个类】主要使用了【Template模式】。

---------------------

编写【示例代码】或者【测试代码】时，我们强烈推荐【使用 HSQLDB 这个数据库】，它是一个【用Java编写的关系数据库】，可以以【内存模式】或者【文件模式】运行，本身【只有一个jar包】，非常适合【演示代码】或者【测试代码】。

我们以【实际工程】为例，
1. 先【创建Maven工程spring-data-jdbc】，然后【引入以下依赖】：
--------------------------------------------------
<dependencies>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId> <-- spring-context 5.2.0.RELEASE
        <version>5.2.0.RELEASE</version>
    </dependency>

    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId> <-- spring-jdbc 5.2.0.RELEASE
        <version>5.2.0.RELEASE</version>
    </dependency>

    <dependency>
        <groupId>javax.annotation</groupId>
        <artifactId>javax.annotation-api</artifactId> <-- javax.annotation-api 1.3.2
        <version>1.3.2</version>
    </dependency>

    <dependency>
        <groupId>com.zaxxer</groupId>
        <artifactId>HikariCP</artifactId> <-- HikariCP 3.4.2
        <version>3.4.2</version>
    </dependency>

    <dependency>
        <groupId>org.hsqldb</groupId>
        <artifactId>hsqldb</artifactId> <-- hsqldb 2.5.0
        <version>2.5.0</version>
    </dependency>

</dependencies>
--------------------------------------------------


在【AppConfig】中，我们需要创建【以下几个必须的Bean】： */
@Configuration 
@ComponentScan 
@PropertySource("jdbc.properties") 
public class AppConfig {

	@Value("${jdbc.url}")
	String jdbcUrl;

	@Value("${jdbc.username}")
	String jdbcUsername;

	@Value("${jdbc.password}")
	String jdbcPassword;

	@Bean 
	DataSource createDataSource() {
		HiKariConfig config = new HikariConfig();

		config.setJdbcUrl(jdbcUrl);
		config.setUsername(jdbcUsername);
		config.setPassword(jdbcPassword);

		config.addDataSourceProperty("autoCommit", "true");
		config.addDataSourceProperty("connectionTimeout", "5");
		config.addDataSourceProperty("idleTimeout", "60");

		return new HikariDataSource(config);
	}

	@Bean 
	JdbcTemplate createJdbcTemplate(@Autowired DataSource dataSource) {
		return new JdbcTemplate(dataSource);
	}
}


/*
在上述配置中：

	1. 通过@PropertySource("jdbc.properties")读取数据库配置文件；
	
	2. 通过@Value("${jdbc.url}")注入配置文件的相关配置；
	
	3. 创建一个DataSource实例，它的实际类型是HikariDataSource，创建时需要用到注入的配置；
	
	4. 创建一个JdbcTemplate实例，它需要注入DataSource，这是通过方法参数完成注入的。


最后，针对【HSQLDB】写一个【配置文件jdbc.properties】： 
----------------------------------------------
# 数据库文件名为testdb:
jdbc.url=jdbc:hsqldb:file:testdb

# Hsqldb默认的用户名是sa，口令是空字符串:
jdbc.username=sa
jdbc.password=

--------------------------------------------------

可以通过【HSQLDB自带的工具】来初始化【数据库表】，这里我们【写一个Bean】，在【Spring容器启动时】自动创建【一个users表】： */
@Component 
public class DatabaseInitializer {
	@Autowired 
	JdbcTemplate jdbcTemplate;

	@PostConstruct 
	public void init() {
		jdbcTemplate.update("Create Table if not exists users (" 
			+ "id BigInt Identity Not Null Primary Key , "
			+ "email Varchar(100) Not Null , "
			+ "password Varchar(100) Not Null , "
			+ "name Varchar(100) Not Null , "
			+ "Unique (email) "
			+ ");"  
		);
	}
}

/*
现在，所有准备工作都已完毕。我们只需要在需要访问数据库的Bean中，注入JdbcTemplate即可： */
@Component 
public class UserService {
	@Autowired 
	JdbcTemplate jdbcTemplate;
}


/*==============================================================


#	JdbcTemplate用法 


【Spring提供的JdbcTemplate】采用【Template模式】，提供了【一系列以回调为特点的工具方法】，目的是【避免繁琐的try...catch语句】。

我们以具体的示例来说明【JdbcTemplate的用法】。

首先我们看【T execute(ConnectionCallback<T> action)方法】，它提供了【Jdbc的Connection】供我们使用： */
public User getUserById(long id) {
	// 注意传入的是【ConnectionCallback】
	return jdbcTemplate.execute((Connection conn) -> {
	 	// 可以直接使用【conn实例】, 【不要释放它】, 回调结束后【JdbcTemplate自动释放】

	 	// 在内部【手动创建 PreparedStatement、ResultSet】必须用【try(...)释放】
	 	try (var ps = conn.prepareStatement("Select * From users Where id = ?")) {

	 		ps.setObject(1, id);

	 		try (var rs = ps.executeQuery()) {

	 			if (rs.next()) {
	 				return new User( // new User object
	 					rs.getLong("id"), // id
	 					rs.getString("email"), // email
	 					rs.getString("password"), // password
	 					rs.getString("name") // name
	 				);

	 				throw new RuntimeException("user not found by id.");
	 			}

	 		}

	 	}
	 });
}

/*
也就是说，【上述回调方法】允许【获取Connection】，然后【做任何 基于Connection的操作】。

我们再看
T execute(String sql, PreparedStatementCallback<T> action)的用法： */
public User getUserByName(String name) {
	// 需要传入SQL语句, 以及PreparedStatementCallback
	return jdbcTemplate.execute("Select * From uers Where name = ?", (PreparedStatement ps) -> {

		// 【PreparedStatement实例】已经由【JdbcTemplate创建】, 并在【回调后】自动释放
		ps.setObject(1, name);

		try (var rs = ps.executeQuery()) {
			if (rs.next()) {
				return new User( // new User object
					rs.getLong("id"), // id
					rs.getString("email"), // email
					rs.getString("password"), // password
					rs.getString("name")
				); // name
			}

			throw new RuntimeException("user not found by id.");
		}
	});
}

/*
最后，我们看
 T queryForObject(String sql, Object[] args, RowMapper<T> rowMapper)方法
： */
public User getUserByEmail(String email) {
	// 传入SQL, 参数和RowMapper实例
	return jdbcTemplate.queryForObject("Select * From users Where email = ?", new Object[] { email }, (ResultSet rs, int rowNum) -> {

		// 将【ResultSet的当前行】, 映射为【一个JavaBean】
		return new User(
			// new User object
			rs.getLong("id"), // id
			rs.getString("email"), // email
			rs.getString("password"), // password
			rs.getString("name")
		);
	});
}

/*
在【queryForObject()方法】中，【传入SQL】以及【SQL参数】后，【JdbcTemplate】会自动【创建PreparedStatement】，自动执行【查询】并【返回ResultSet】，我们提供的【RowMapper】需要做的事情就是【把ResultSet的当前行】映射成【一个JavaBean】并【返回】。

-----------------------------------

整个过程中，【使用Connection、PreparedStatement和ResultSet】都【不需要我们手动管理】。

【RowMapper】不一定【返回JavaBean】，实际上它可以【返回任何Java对象】。

例如，使用【SELECT COUNT(*) 查询时】，可以【返回Long】： */
public long getUsers() {
	return jdbcTemplate.queryForObject("Select Count(*) From users", null, (ResultSet rs, int rowNum) -> {
		// Select Count(*)查询只有一列, 取第一列数据
		return rs.getLong(1);
	});
}

/*
如果我们期待【返回多行记录】, 而不是一行, 可以用【query()方法】: */
public List<User> getUsers(int pageIndex) {
	int limit = 100;
	int offset = limit * (pageIndex - 1);

	return jdbcTemplate.query("Select * From users Limit ? Offset ?", new Object[] { limit, offset }, new BeanPropertyRowMapper<>(User.class));
}

/*
【上述query()方法】传入的【参数】仍然【是SQL、SQL参数、RowMapper实例】。

这里我们直接使用【Spring】提供的【BeanPropertyRowMapper】。

如果【数据库表的结构】恰好和【JavaBean的属性名称】一致，那么【BeanPropertyRowMapper】就可以直接把【一行记录】按【列名】转换为【JavaBean】。

如果我们执行的【不是查询，而是插入、更新和删除操作】，那么需要【使用update()方法】： */
public void updateUser(User user) {
	// 传入SQL, SQL参数, 返回更新的行数
	if (1 != jdbcTemplate.update("Update user Set name = ? Where id = ?", user.getName(), user.getId())) {

		throw new RuntimeException("User not found by id");
	}
}

/*
只有【一种INSERT操作】比较特殊，那就是【如果某一列是自增列】（例如自增主键）

通常，我们需要获取【插入后的自增值】。

【JdbcTemplate】提供了【一个KeyHolder】来【简化这一操作】： */
public User register(String email, String password, String name) {
	// 创建一个KeyHolder
	KeyHolder holder = new GenerateKeyHolder();

	if (1 != jdbcTemplate.update(
		// 参数1:PreparedStatementCreator
		(conn) -> {
			// 【创建PreparedStatement】时, 必须【指定Return_Generated_Keys】
			var ps = conn.prepareStatement("Insert into users(email, password, name) Values(?, ?, ?)", Statement.Return_Generated_Keys);

			ps.setObject(1, email);
			ps.setObject(2, password);
			ps.setObject(3, name);

			return ps;
		},
		// [参数2: KeyHolder]
		holder)
	) {
		throw new RuntimeException("Insert failed.");
	}

	// 从【KeyHolder】中, 获取【返回的自增值】
	return new User(holder.getKey().longValue(), email, password, name);
}

/*
【JdbcTemplate】还有【许多"重载方法"】，这里我们不一一介绍。

需要强调的是，【JdbcTemplate】只是对【JDBC操作的一个简单封装】，【它的目的】是【尽量减少 手动编写 try(resource) {...} 的代码】

对于【查询】，【主要通过RowMapper】实现了【JDBC结果集】到【Java对象】的[转换]。

-----------------------------------------------


我们总结一下【JdbcTemplate的用法】，那就是：

	· 针对【简单查询】，优选【query() 和 queryForObject()】，因为只需提供【SQL语句、参数、RowMapper】；

	· 针对【更新操作】，【优选update()方法】，因为只需提供【SQL语句、参数】；

	· 任何复杂的操作，最终也可以通过【execute(ConnectionCallback)】实现，因为拿到【Connection】就可以做【任何JDBC操作】。


实际上我们使用最多的仍然是【各种查询】。

如果在【设计表结构的时候】，能够和【JavaBean的属性】一一对应，那么【直接使用BeanPropertyRowMapper】就很方便。

如果【表结构】和【JavaBean】不一致怎么办？
那就需要稍微【改写一下查询】，使【结果集的结构】和【JavaBean】保持一致。

例如，【表的列名】是【office_address】，而【JavaBean属性】是【workAddress】，就需要【指定别名】

【改写查询】如下：

	SELECT id, email, office_address AS workAddress, name FROM users WHERE email = ?


===========================================================


#	练习

使用JdbcTemplate */





/*========================================================

#	----- 使用JDBC の 小结 ----- 

(使用HSQLDB这个数据库 测试/演示)


1. 【Spring】提供了【JdbcTemplate】来简化【JDBC操作】；

2. 【使用JdbcTemplate时】，根据需要【优先选择高级方法】；

3. 【任何JDBC操作】都可以使用【保底的execute(ConnectionCallback)方法】。

========================

汇总:
JdbcTemplate的用法，那就是：

	1. 针对【简单查询】，优选
		· query()
		· queryForObject()

	因为只需提供【SQL语句】、【参数】和【RowMapper】；
	

	2. 针对【更新操作】，优选
		· update()

	因为只需提供SQL语句和参数；
	
	
	3. 【任何复杂的操作】，最终也可以通过
		· execute(ConnectionCallback) 实现

	因为【拿到Connection】就可以做【任何JDBC操作】。


==================

代码模板:  
==========

1.针对HSQLDB写一个配置文件jdbc.properties：
------------------------------------------
# 数据库文件名为testdb:
jdbc.url=jdbc:hsqldb:file:testdb

# Hsqldb默认的用户名是sa，口令是空字符串:
jdbc.username=sa
jdbc.password=
------------------------------------------


2. 通过HSQLDB自带的工具来初始化数据库表，这里我们写一个Bean，在Spring容器启动时自动创建一个users表： */
@Component 
public class DatabaseInitializer {

	@Autowired 
	JdbcTemplate jdbcTemplate;

	@PostConstruct // 提前构造
    public void init() {
        jdbcTemplate.update("CREATE TABLE IF NOT EXISTS users (" //
                + "id BIGINT IDENTITY NOT NULL PRIMARY KEY, " //
                + "email VARCHAR(100) NOT NULL, " //
                + "password VARCHAR(100) NOT NULL, " //
                + "name VARCHAR(100) NOT NULL, " //
                + "UNIQUE (email))");
        // jdbcTemplate.update();
    }
}


/*
我们只需要在【需要访问数据库 的 Bean/Component】中， 【注入JdbcTemplate】即可： */
@Component
public class UserService {

    @Autowired
    JdbcTemplate jdbcTemplate;

    //...
}


/*
T execute(ConnectionCallback<T> action)

它提供了Jdbc的Connection供我们使用： */
public User getUserById(long id) {
    // 注意传入的是ConnectionCallback:
    return jdbcTemplate.execute( (Connection conn) -> {
        // 可以【直接使用 conn实例】，不要释放它，回调结束后【JdbcTemplate自动释放】:

        // 对比: 在内部 手动创建的 PreparedStatement、ResultSet 必须【用try(...)释放】:
        try (var ps = conn.prepareStatement("SELECT * FROM users WHERE id = ?")) {

            ps.setObject(1, id);

            try (var rs = ps.executeQuery()) {
            	// prepareStatement.executeQuery()

                if (rs.next()) {
                    return new User( // new User object:
                            rs.getLong("id"), // id
                            rs.getString("email"), // email
                            rs.getString("password"), // password
                            rs.getString("name")); // name
                }

                throw new RuntimeException("user not found by id.");
            }
        }
    });
}


// execute("sql", PreparedStatementCallback ps)
public User getUserByName(String name) {
    // 需要传入SQL语句，以及PreparedStatementCallback:
    return jdbcTemplate.execute("SELECT * FROM users WHERE name = ?", (PreparedStatement ps) -> {

        // 【PreparedStatement实例】已经由【JdbcTemplate】创建，并在【回调后】自动释放:
        ps.setObject(1, name);

        try (var rs = ps.executeQuery()) {

            if (rs.next()) {
                return new User( // new User object:
                        rs.getLong("id"), // id
                        rs.getString("email"), // email
                        rs.getString("password"), // password
                        rs.getString("name")); // name
            }

            throw new RuntimeException("user not found by id.");
        }
    });
}


// queryForObject("sql p = ?", parameter, RowMapper = (ResultSet, rowNum))
public User getUserByEmail(String email) {
    // 传入SQL，参数和RowMapper实例:
    return jdbcTemplate.queryForObject("SELECT * FROM users WHERE email = ?", new Object[] { email },
            (ResultSet rs, int rowNum) -> {

                // 将【ResultSet的当前行】映射为【一个JavaBean】
                return new User( // new User object:
                        rs.getLong("id"), // id
                        rs.getString("email"), // email
                        rs.getString("password"), // password
                        rs.getString("name")); // name
        });
}


// queryForObject("sql", parameter, (ResultSet, rowNum))
public long getUsers() {
    return jdbcTemplate.queryForObject("SELECT COUNT(*) FROM users", null, (ResultSet rs, int rowNum) -> {

        // SELECT COUNT(*) 查询 【只有一列，取第一列数据】
        return rs.getLong(1);
    });
}


// update() 操作: 插入、更新、删除
// update("sql", parameter.. )
public void updateUser(User user) {
    // 传入SQL，SQL参数，返回更新的行数:
    if (1 != jdbcTemplate.update("UPDATE user SET name = ? WHERE id=?", user.getName(), user.getId())) {

    	//...

        throw new RuntimeException("User not found by id");
    }
}





